Run JavaScript in Power Automate

Why would I ever need JS in Power Automate?

Coming from a pro-code background, I’ve always found the Power Platform to be interesting and cool, but often a bit cumbersome, to say the least, I love the idea of citizen developers to be able to build tools that helps them grow their business, but I haven’t personally spend too much time with it!

It wasn’t until recently where I was training a new colleague who’s new to Microsoft 365, and comes from a pro-code background that I really ended up scratching my head, and came across this feature, a customer wanted a flow that whenever a mail arrived in a shared mailbox with a subject that contained a specific pattern any attachments would be uploaded to a SharePoint document library.

Oh, what a great case for Power Automate - a simple trigger on new mail, a quick RegEx, and upload attachments - should be easy … Well as it turned out after I had scoped the task and assigned in to him, there is no non-premium RegEx testers in Power Automate.

Well crap! - Well, there has to be a way, quick Google search later I came across a tweet suggesting that Office Scripts could be triggered from Power Automate, NOW this opens a whole new world - cause we can now run any JavaScript code in Power Automate.

The implementation

What impressed me the most was just how easy it was to implement, let’s have a look! - Firstly I’m going to be assuming that you’ve got a flow already setup and am just currently stuck on implementing a little thing, so that’s where we’ll start!

Since we’ll be using excel for this we’ll need to create an excel file and store it somewhere, if at all possible I would recommend a location only you have access to, that way you’re sure no one will ever come across it and end up breaking your flow - you could also slowly build your own little library over time that you copy with you from solution to solution.

Once in Excel Online you should see a “new” ribbon tab called Automate (NOTE, you’ll need a Microsoft 365 license with access to the Microsoft 365 Office desktop apps - despite this not actually working in the desktop apps…)

To those of you who’ve ever used macros this will look fairly familiar, and to those of you who haven’t had to deal with them … congratulation!

From here we’ll click the new script button, from where we’ll have the option to create a new script, for this sample I’ll be creating a AddTwoDigits function, just to show the concept.

My code ends up looking like this (I know it’s technically TypeScript, but just roll with it)

function main(workbook: ExcelScript.Workbook, num1: number, num2: number): number {
    return num1 + num2;
}

You’ll notice that our first parameter “workbook” is of the type ExcelScript.Workbook - this is a JS reference to the spreadsheet if you wish to read/write to the sheet, for our case here I’ve just added two more parameters, the two digits we want to add together, and a very simple return.

It looks like this in practice

From here it’s as easy as pie to use it in Power Automate, simply add the “Run Script” trigger from Excel in Power Automate

And after running the flow this is what we get

There you go - You’ve just successfully ran JavaScript/TypeScript code from Power Automate!

Notice the “logs” array? - that’s a string array of all the console.log’s you’ve printed, prefixed with a timestamp, like below:

  "logs": [
    "[2022-12-09T19:04:17.9850Z] Adding 1 and 2",
    "[2022-12-09T19:04:18.0010Z] The result is 3"
  ]

If you’re anything like me you’re about to attempt to use the fetch API this way to avoid the premium connector, unfortunately Microsoft already thought of this, so no dice.

Conclusions

This is a really cool technology, and really highlights some of the crazy ways you make Microsoft’s different technologies play together, and I absolutely love it, and since discovering it, I’ve used it a few times to help out my low-code colleagues, when they hit a roadblock or something (like RegEx) that isn’t really supported in Power Automate, however it’s a fairly vulnerable and obfuscated way to solve a simple problem, I wish Microsoft would bring the JavaScript action from Logic Apps to Power Automate, and at the same time I get why they haven’t.

For me the biggest problem with this approach is how easily it breaks if someone somehow gets rid of you excel sheet, i.e IT deletes the site it’s on cause of inactivity, a user deletes it cause “no one is using it” - and at least for me the OneDrive “Location” option was broken - that might also just be my dev tenant, I’m not quite sure.

TL;DR

You can use Excel Script to run JavaScript in Power Automate.